package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.AgentDataList;
import com.mbc.rmt.entities.Agent;
import com.mbc.rmt.entities.AgentBalanceRpt;
import com.mbc.rmt.entities.AgentLedgerRpt;
import com.mbc.rmt.entities.AgentRpt;

public class AgentDAOImpls extends DAO<Agent> implements AgentDAO{
	
	public AgentDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "Agent";
	}

	@Override
	public void create(Agent entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( agent_id, agent_code, name, myanmar_name, nrc, address, phone, fax, email_address, account_set_id, waitingaccount_set_id,  officeaccount_set_id, sentaccount_set_id, type_id, IsDefault, branchID, status) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i=1;
		pstmt.setLong(i++, entity.getAgent_id());
		pstmt.setString(i++, entity.getAgent_code());
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getMyanmarname());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setString(i++, entity.getFax());
		pstmt.setString(i++, entity.getEmailaddress());
		
		//pstmt.setString(i++, entity.getAccount_set_id());
		if(entity.getAccountset_id() != null)
			pstmt.setLong(i++, entity.getAccountset_id());
		else
			pstmt.setString(i++, null);
		if(entity.getWaitingaccount_set_id() !=null)
			pstmt.setLong(i++, entity.getWaitingaccount_set_id());
		else
			pstmt.setString(i++, null);
		if(entity.getOfficeaccount_set_id() !=null)
			pstmt.setLong(i++, entity.getOfficeaccount_set_id());
		else
			pstmt.setString(i++, null);
		if(entity.getSentaccount_set_id() != null)
			pstmt.setLong(i++, entity.getSentaccount_set_id());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getType_id());
		pstmt.setBoolean(i++, entity.isIsdefault());
		pstmt.setLong(i++, entity.getBranch_id());
		pstmt.setInt(i++, entity.getSavingstatus());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(Agent entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set "
				+ " agent_code = ?, name = ?, myanmar_name = ?, nrc = ?, address = ?, phone = ?, fax = ?, email_address = ?, account_set_id = ?, waitingaccount_set_id = ?, sentaccount_set_id = ?, officeaccount_set_id = ?, type_id = ?, IsDefault = ?, branchID = ?"
				+ " where agent_id =  " + entity.getAgent_id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getAgent_code());
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getMyanmarname());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setString(i++, entity.getFax());
		pstmt.setString(i++, entity.getEmailaddress());
		//pstmt.setString(i++, entity.getAccount_set_id());
		if(entity.getAccountset_id() != null)
			pstmt.setLong(i++, entity.getAccountset_id());
		else
			pstmt.setString(i++, null);
		if(entity.getWaitingaccount_set_id() != null)
			pstmt.setLong(i++, entity.getWaitingaccount_set_id());
		else
			pstmt.setString(i++, null);
		if(entity.getSentaccount_set_id() != null)
			pstmt.setLong(i++, entity.getSentaccount_set_id());
		else
			pstmt.setString(i++, null);
		if(entity.getOfficeaccount_set_id() != null)
			pstmt.setLong(i++, entity.getOfficeaccount_set_id());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getType_id());
		pstmt.setBoolean(i++, entity.isIsdefault());
		pstmt.setLong(i++, entity.getBranch_id());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where agent_id = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	protected Agent readRecord(ResultSet rs) throws SQLException {
		Agent l_entity = new Agent();
		l_entity.setAgent_id(rs.getLong("agent_id"));
		l_entity.setAgent_code(rs.getString("agent_code"));
		l_entity.setName(rs.getString("name"));
		l_entity.setMyanmarname(rs.getString("myanmar_name"));
		l_entity.setNrc(rs.getString("nrc"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setPhone(rs.getString("phone"));
		l_entity.setFax(rs.getString("fax"));
		l_entity.setEmailaddress(rs.getString("email_address"));
		l_entity.setAccountset_id(rs.getLong("account_set_id"));
		l_entity.setAccountset_code(rs.getString("account_set_code"));
		l_entity.setWaitingaccount_set_id(rs.getLong("waitingaccount_set_id"));
		l_entity.setWaitingaccount_set_code(rs.getString("waiting_coa_code"));
		l_entity.setSentaccount_set_id(rs.getLong("sentaccount_set_id"));
		l_entity.setSentaccount_set_code(rs.getString("send_coa_code"));
		l_entity.setOfficeaccount_set_id(rs.getLong("officeaccount_set_id"));
		l_entity.setOfficeaccount_set_code(rs.getString("office_coa_code"));
		l_entity.setType_id(rs.getLong("type_id"));
		l_entity.setType_code(rs.getString("type_code"));
		l_entity.setIsdefault(rs.getBoolean("isdefault"));
		l_entity.setSavingstatus(rs.getInt("status"));
		return l_entity;
	}
	@Override
	public Agent findByCode(String code,long branchID) throws SQLException {
		Agent l_Agent = null;
		query = " select row_number() over(order by agent_id)as [rowno], "
				+ "a.agent_id,a.agent_code,a.name,a.myanmar_name, " 
				+ "a.nrc,a.address,a.phone,a.fax, " 
				+ "a.email_address,isnull(a.account_set_id,0)account_set_id, "
				+ "isnull(acoa.agent_coa_code,'')account_set_code, " 
				+ "isnull(a.waitingaccount_set_id,0)waitingaccount_set_id,isnull(wcoa.waiting_coa_code,'')waiting_coa_code, "
				+ "isnull(a.sentaccount_set_id,0)sentaccount_set_id,isnull(scoa.send_coa_code,'')send_coa_code, "
				+ "isnull(a.officeaccount_set_id,0)officeaccount_set_id,isnull(ocoa.office_coa_code,'')office_coa_code, "  
				+ "tp.type_id,tp.type_code,a.IsDefault ,a.status "
				+ "From Agent a "
				+ "left join WaitingCOA wcoa on wcoa.waiting_coa_id = a.account_set_id "
				+ " left join OfficeCOA ocoa on ocoa.office_coa_id = a.account_set_id "
				+ " left join SendCOA scoa on scoa.send_coa_id = a.account_set_id "
				+ "left join AgentCOA acoa on acoa.agent_coa_id = a.account_set_id "
				+ "left join Type tp on tp.type_id = a.type_id " 
				+ " where Agent_Code = '" + code + "' and a.branchID = " + branchID;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Agent = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Agent;
	}
	@Override
	public AgentDataList findAgents(Agent cri) throws SQLException {
		AgentDataList l_DataList = new AgentDataList();
		ArrayList<Agent> l_entityList = new ArrayList<Agent>();
		/*String l_cri = "and (agent_code like N'%" + cri.getKeyword() + "%'"
				+ " or name like N'%" + cri.getKeyword() + "%')";*/
		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() +"%')";
		
		if(!cri.getStatus().equals("HO_TRAN"))
			l_cri += " and a.branchID = " + cri.getBranchId();
		else {
			if(cri.getHo_branchid() != 0)
				l_cri += " and a.branchID in (" + cri.getHo_branchid() + ", " + cri.getBranchId() + ")";
			else
				l_cri += " and a.branchID = " + cri.getBranchId();
		}
		
		if(cri.getStatus().equals("TRAN"))
			l_cri += " and a.[status] = " + 0 ;
		else if(cri.getStatus().equals("SAVING"))
			l_cri += " and a.[status] = " + 1 ;
		else if(cri.getStatus().equals("DEFINE_PAYMENT_A"))
			l_cri += " and a.[status] = " + 0 + " and tp.type_code = 'A' ";
		else if(cri.getStatus().equals("DEFINE_PAYMENT_B"))
			l_cri += " and a.[status] = " + 0 + " and tp.type_code = 'B' ";
		else if(cri.getStatus().equals("HO_TRAN"))
			l_cri += " and a.[status] = " + 0;
		else if(cri.getStatus().equals("CRSBYAGENT") || cri.getStatus().equals("HO"))
			l_cri += " and a.[status] = " + 0 +" and (type_code = 'A' or type_code = 'B') ";
		
		if(cri.getStatus().equals("CONFIRM"))
			l_cri += " and a.[status] = " + 0;
		
		/*String colAgentIDquery = "";
		String colAgentID = " ";
		if(cri.getStatus().equals("true"))
			colAgentID = " and agent_id <> "+cri.getBranchId();*/
				
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by agent_id)as [rowno], "
				+ "a.agent_id,a.agent_code,a.name,a.myanmar_name, " 
				+ "a.nrc,a.address,a.phone,a.fax,a.branchID, " 
				+ "a.email_address,isnull(a.account_set_id,'')account_set_id, "
				+ "isnull(acoa.agent_coa_code,'')account_set_code, " 
				+ "isnull(a.waitingaccount_set_id,'')waitingaccount_set_id,isnull(wcoa.waiting_coa_code,'')waiting_coa_code, "
				+ "isnull(a.sentaccount_set_id,'')sentaccount_set_id,isnull(scoa.send_coa_code,'')send_coa_code, "
				+ "isnull(a.officeaccount_set_id,'')officeaccount_set_id,isnull(ocoa.office_coa_code,'')office_coa_code, " 
				+ "tp.type_id,tp.type_code,a.IsDefault,a.status "
				+ "From Agent a "
				+ "left join WaitingCOA wcoa on wcoa.waiting_coa_id = a.waitingaccount_set_id "
				+ "left join OfficeCOA ocoa on ocoa.office_coa_id = a.officeaccount_set_id "
				+ "left join SendCOA scoa on scoa.send_coa_id = a.sentaccount_set_id "	
				+ "left join AgentCOA acoa on acoa.agent_coa_id = a.account_set_id "
				+ "left join Type tp on tp.type_id = a.type_id " 
				+ "where 1 = 1" + l_cri 
//				+ colAgentID 
				+ ") " + table_name + " where rowno > "
				+ cri.getIndex(); //+ " and branchid = " + cri.getBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = "and (agent_code like N'%" + cri.getKeyword() + "%'"
				+ " or name like N'%" + cri.getKeyword() + "%'";
		String l_cri2 = "and (agent_code like N'%" + cri.getKeyword() + "%'"
				+ " or name like N'%" + cri.getKeyword() + "%'"
				+ " and a.branchID like '%" + cri.getKeyword() + "%')";
		if(!cri.getStatus().equals("HO_TRAN"))
				l_cri1 += " and branchID like '%" + cri.getKeyword() + "%')";
		else
			l_cri1 += ") and branchID IN (" + cri.getHo_branchid() + ", " + cri.getBranchId() + ")";
		
		if(cri.getStatus().equals("TRAN"))
			l_cri1 += " and [status] = " + 0 ;
		else if(cri.getStatus().equals("SAVING"))
			l_cri1 += " and [status] = " + 1 ;
		
		if(cri.getStatus().equals("DEFINE_PAYMENT_A"))
			query = "select count(agent_id) from Agent a " +
					"left join Type tp on tp.type_id = a.type_id " +
					"where a.branchId = " + cri.getBranchId() + " and a.[status] = " + 0 + " and tp.type_code = 'A' " +
					"and (agent_code like N'%" + cri.getKeyword() + "%' or [name] like N'%" + cri.getKeyword() + "%' and a.branchId like '%" + cri.getKeyword() + "%')";
		else if(cri.getStatus().equals("DEFINE_PAYMENT_B"))
			query = "select count(agent_id) from Agent a " +
					"left join Type tp on tp.type_id = a.type_id " +
					"where a.branchId = " + cri.getBranchId() + " and a.[status] = " + 0 + " and tp.type_code = 'B' " +
					"and (agent_code like N'%" + cri.getKeyword() + "%' or [name] like N'%" + cri.getKeyword() + "%' and a.branchId like '%" + cri.getKeyword() + "%')";
		else if(cri.getStatus().equals("HO_TRAN"))
			query = "select count(agent_id) from " + table_name + " where 1 = 1 " + l_cri1;
		else if(cri.getStatus().equals("CRSBYAGENT") || cri.getStatus().equals("HO"))
		    query = " select count(agent_id) from Agent a " +
                    " left join Type t on t.type_id=a.type_id " +
                    " where a.branchID = " + cri.getBranchId() + l_cri2 +
                    " and a.[status] = 0 and (t.type_code = 'A' or t.type_code = 'B') ";
		else
			query = "select count(agent_id) from " + table_name + " where branchID = " +cri.getBranchId()+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	private void AgentRptParameter(CallableStatement l_pstmt,
			AgentRpt agentRpt) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, agentRpt.getFromagentID());
		l_pstmt.setString(i++, agentRpt.getToagentID());
		l_pstmt.setString(i++, agentRpt.getFromcoaID());
		l_pstmt.setString(i++, agentRpt.getTocoaID());
		l_pstmt.setLong(i++, agentRpt.getBranchID());
	}
	public AgentDataList getAgentList(AgentRpt agentRpt) throws SQLException {
		AgentDataList l_DataList = new AgentDataList();
		ArrayList<Agent> l_AgentList = new ArrayList<Agent>();
		String l_query = "dbo.sp_cr_AgentRpt ?, ?, ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentRptParameter(l_pstmt, agentRpt);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_AgentList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_AgentList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}
	private static Agent readRecordFT(ResultSet rs) throws SQLException {
		Agent l_entity = new Agent();
		l_entity.setAgent_code(rs.getString("agent_code"));
		l_entity.setName(rs.getString("name"));
		l_entity.setNrc(rs.getString("nrc"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setPhone(rs.getString("phone"));
		l_entity.setFax(rs.getString("fax"));
		l_entity.setEmailaddress(rs.getString("email_address"));
		l_entity.setAccountset_code(rs.getString("agent_coa_code"));
		l_entity.setType_code(rs.getString("type_code"));
		return l_entity;
	}

	@Override
	public ResultSet prepareAgentReport(AgentRpt agentRpt) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_AgentRpt ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentRptParameter(l_pstmt, agentRpt);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	protected Agent readRecords(ResultSet rs) throws SQLException {
		Agent l_entity = new Agent();
		l_entity.setAgent_id(rs.getLong("agent_id"));
		l_entity.setAgent_code(rs.getString("agent_code"));
		l_entity.setName(rs.getString("name"));
		return l_entity;
	}

	@Override
	public Agent getAgents(String code,long branchId) throws SQLException {
		Agent l_Agent = null;
		query = getSelectQuery() + " where Agent_Code = '" + code + "' and Branchid = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Agent = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_Agent;
	}

	@Override
	public Agent getAgentsByCurrencyID(Agent agent) throws SQLException {
		Agent l_Agent = null;
		query = "SELECT a.agent_id,a.agent_code,a.name,a.nrc,a.ADDRESS,a.phone,ab.currentbalance FROM agent a"+
				" LEFT JOIN agentbalance ab ON a.agent_id = ab.agent_id WHERE "+
				" agent_code = '"+agent.getAgent_code()+"' AND ab.currencyid = "+agent.getCurrencyid()+
				" and a.branchid = "+agent.getBranch_id()+
				" and a.[status] = 1";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Agent = readRecordbyCurrencyID(rs);
		else
		{
			query = "SELECT a.agent_id,a.agent_code,a.name,a.nrc,a.ADDRESS,a.phone,0 as currentbalance FROM agent a Where "+
					" agent_code = '"+agent.getAgent_code()+
					"' and branchid = "+agent.getBranch_id()+
					" and a.[status] = 1";
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			if (rs.next())
				l_Agent = readRecordbyCurrencyID(rs);
		}
		rs.close();
		pstmt.close();
		return l_Agent;
	}
	
	protected Agent readRecordbyCurrencyID(ResultSet rs) throws SQLException {
		Agent l_entity = new Agent();
		l_entity.setAgent_id(rs.getLong("agent_id"));
		l_entity.setAgent_code(rs.getString("agent_code"));
		l_entity.setName(rs.getString("name"));
		l_entity.setNrc(rs.getString("nrc"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setPhone(rs.getString("phone"));
		l_entity.setCurrentbalance(rs.getDouble("currentbalance"));
		return l_entity;
	}

	@Override
	public ResultSet prepareAgentBalanceReport(AgentBalanceRpt entity)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "[dbo].[sp_cr_AgentBalanceRpt] ?, ?, ?, ?, ? , ? , ? , ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentBalanceRptParameter(l_pstmt, entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private void AgentBalanceRptParameter(CallableStatement l_pstmt,
			AgentBalanceRpt entity) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, entity.getFromagentcode());
		l_pstmt.setString(i++, entity.getToagentcode());
		l_pstmt.setString(i++, entity.getFromtypeid());
		l_pstmt.setString(i++, entity.getTotypeid());
		l_pstmt.setString(i++, entity.getFromcurrencyid());
		l_pstmt.setString(i++, entity.getTocurrencyid());
		l_pstmt.setString(i++, entity.getOrderby());
		l_pstmt.setLong(i++, entity.getBranchid());
	}

	@Override
	public ResultSet prepareAgentLedgerReport(AgentLedgerRpt entity)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "[dbo].[sp_cr_AgentLedger_Rpt] ?, ?, ?, ?, ? , ? , ? , ? , ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentLedgerRptParameter(l_pstmt, entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private void AgentLedgerRptParameter(CallableStatement l_pstmt,
			AgentLedgerRpt entity) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, entity.getFromdate());
		l_pstmt.setString(i++, entity.getTodate());
		l_pstmt.setString(i++, entity.getFromagentid());
		l_pstmt.setString(i++, entity.getToagentid());
		l_pstmt.setString(i++, entity.getFromtypeid());
		l_pstmt.setString(i++, entity.getTotypeid());
		l_pstmt.setString(i++, entity.getCurrencyid());;
		l_pstmt.setBoolean(i++, entity.isSpecified());
		l_pstmt.setLong(i++, entity.getBranchid());
	}

	@Override
	public Agent findById(long agentid) throws SQLException {
		Agent l_Agent = null;
		query = " select row_number() over(order by agent_id)as [rowno], "
				+ "a.agent_id,a.agent_code,a.name,a.myanmar_name, " 
				+ "a.nrc,a.address,a.phone,a.fax, " 
				+ "a.email_address,isnull(a.account_set_id,0)account_set_id, "
				+ "isnull(acoa.agent_coa_code,'')account_set_code, " 
				+ "isnull(a.waitingaccount_set_id,0)waitingaccount_set_id,isnull(wcoa.waiting_coa_code,'')waiting_coa_code, "
				+ "isnull(a.sentaccount_set_id,0)sentaccount_set_id,isnull(scoa.send_coa_code,'')send_coa_code, "
				+ "isnull(a.officeaccount_set_id,0)officeaccount_set_id,isnull(ocoa.office_coa_code,'')office_coa_code, "  
				+ "tp.type_id,tp.type_code,a.IsDefault ,a.status "
				+ "From Agent a "
				+ "left join WaitingCOA wcoa on wcoa.waiting_coa_id = a.account_set_id "
				+ " left join OfficeCOA ocoa on ocoa.office_coa_id = a.account_set_id "
				+ " left join SendCOA scoa on scoa.send_coa_id = a.account_set_id "
				+ "left join AgentCOA acoa on acoa.agent_coa_id = a.account_set_id "
				+ "left join Type tp on tp.type_id = a.type_id " 
				+ " where agent_id = " + agentid ;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Agent = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Agent;
	}

}
